﻿component
{
	include "../../inc/lib.cfm";
	//列表操作
	public any function list(any rc)
	{
		var result = {};
		var offset = (rc.page-1)*rc.rows;
		hql = "from product where " & rc.sqlWhere & rc.sqlSort;
		var result["rows"] = ormExecuteQuery(hql, false, {maxResults=rc.rows, offset=offset});
		var totalhql = "select count(id) as total from product where " & rc.sqlWhere ;
		var result["total"] = ormExecuteQuery(totalhql, true);
		return result;
	}

	//删除操作
	public any function delete(numeric id){
		transaction {
            var product = entityLoadByPk("product", arguments.id);
           	entityDelete(product);
            transactionCommit();
        }
	}

	//获取操作
	public any function get(numeric id){
		result = entityLoadByPk("product", arguments.id);
		//获取picdefault
		if( len(result.getPicDefault())>0 ){
			var pd = entityLoadByPk("uploader", result.getPicDefault());
			result['picdefaultname'] = pd.getClientName();
		}else{
			result['picdefaultname'] = '';
		}


		//获取pics
		if( listlen(result.getPics())>0){
			var pnames = [];
			for(var i =1;i<=listlen(result.getPics());i++){
				var pics = entityLoadByPk("uploader", listgetat(result.getPics(),i));
				pnames.append(pics.getClientName());
			}
			result['picsname'] = pnames;
		}else{
			result['picsname'] = '';
		}

		//获取gmp
		if( len(result.getGmp())>0 ){
			var gmp = entityLoadByPk("uploader", result.getGmp());
			result['gmpname'] = gmp.getClientName();
		}else{
			result['gmpname'] = '';
		}

		//获取registerfile
		if( len(result.getRegisterfile())>0 ){
			var rf = entityLoadByPk("uploader", result.getRegisterfile());
			result['registerfilename'] = rf.getClientName();
		}else{
			result['registerfilename'] = '';
		}

		//获取qualification
		if( listlen(result.getPics())>0){
			var qfnames = [];
			for(var i =1;i<=listlen(result.getQualification());i++){
				var qf = entityLoadByPk("uploader", listgetat(result.getQualification(),i));
				qfnames.append(qf.getClientName());
			}
			result['qualificationname'] = qfnames;
		}else{
			result['qualificationname'] = '';
		}

		//获取supplierid
		if( len(result.getsupplierid())>0 ){
			var sp = entityLoadByPk("supplier", result.getsupplierid());
			result['suppliername'] = sp.getsupchsname();
		}else{
			result['suppliername'] = '';
		}

		//获取categoryids
		query name="q"{
			echo("SELECT productcategory.categoryid, category.text FROM category, productcategory where category.id = productcategory.categoryid and productcategory.productid = ");
			queryparam cfsqltype="cf_sql_integer" value="#result.getid()#";
		}
		var categoryids = valuelist(q.categoryid);
		var categoryidsnames = valuelist(q.text);

		result['categoryids'] = categoryids;
		result['categorynames'] = categoryidsnames;

		return result;
	}

	//新增操作
	public any function save(any rc){
		transaction {
            var q = entityNew("product");

            //如果英文名称没有数据，自动转换中文名称到英文名称
			if( len(trim(rc.proenname)) == 0){
				var proenname = translateToEn(rc.prochsname);
				q.setproenname(proenname);
			}

			//中文全拼
			if( len(trim(rc.propyfullspell)) == 0  ){
				var propyfullspell = getPySpell(rc.prochsname);
				q.setpropyfullspell(propyfullspell);
			}

			//中文拼音首字母
			if( len(trim(rc.propysimplespell)) == 0 ){
				var propysimplespell 	= getPySpell(rc.prochsname,1);
				q.setpropysimplespell(propysimplespell);
			}


			for(var i=1;i<= listlen(rc.categoryids);i++){
				var c = entityNew('productcategory');
				c.setCategoryId( listgetAt(rc.categoryids,i) );
				q.addProductcategory(c);
				entitySave(c);
			}

			include "../../inc/saveandupdate.cfm";
        }
        return q;
	}

	//修改操作
	public any function update(any rc){
		transaction {
            var q = entityLoadByPk("product", rc.id);
			include "../../inc/saveandupdatenotcommit.cfm";
			//如果英文名称没有数据，自动转换中文名称到英文名称
			if( len(trim(rc.proenname)) == 0){
				var proenname = translateToEn(rc.prochsname);
				q.setproenname(proenname);
			}

			//中文全拼
			if( len(trim(rc.propyfullspell)) == 0  ){
				var propyfullspell = getPySpell(rc.prochsname);
				q.setpropyfullspell(propyfullspell);
			}

			//中文拼音首字母
			if( len(trim(rc.propysimplespell)) == 0 ){
				var propysimplespell 	= getPySpell(rc.prochsname,1);
				q.setpropysimplespell(propysimplespell);
			}

			ormExecuteQuery("delete from productcategory where productid = #q.getid()#", true);

			for(var i=1;i<= listlen(rc.categoryids);i++){
				var c = entityNew('productcategory');
				c.setCategoryId( listgetAt(rc.categoryids,i) );
				q.addProductcategory(c);
				entitySave(c);
			}

			entitySave(q);
			transactionCommit();
        }
        return q;
	}

	public any function indexbat(rc){
		result = ORMExecuteQuery("update product set isindex=:isindex WHERE id in (#rc.ids#)",{isindex = lsparsenumber(rc.value)});
		return result;
	}

	public any function topbat(rc){
		result = ORMExecuteQuery("update product set istop=:istop WHERE id in (#rc.ids#)",{istop = lsparsenumber(rc.value)});
		return result;
	}

	public any function newbat(rc){
		result = ORMExecuteQuery("update product set isnew=:isnew WHERE id in (#rc.ids#)",{isnew = lsparsenumber(rc.value)});
		return result;
	}

	public any function hotbat(rc){
		result = ORMExecuteQuery("update product set ishot=:ishot WHERE id in (#rc.ids#)",{ishot = lsparsenumber(rc.value)});
		return result;
	}

	public any function specialbat(rc){
		result = ORMExecuteQuery("update product set isspecial=:isspecial WHERE id in (#rc.ids#)",{isspecial = lsparsenumber(rc.value)});
		return result;
	}

	public any function channellbat(rc){
		result = ORMExecuteQuery("update product set ischannell=:ischannell WHERE id in (#rc.ids#)",{ischannell = lsparsenumber(rc.value)});
		return result;
	}

	public any function salebat(rc){
		result = ORMExecuteQuery("update product set issale=:issale WHERE id in (#rc.ids#)",{issale = lsparsenumber(rc.value)});
		return result;
	}

	public any function statusbat(rc){
		result = ORMExecuteQuery("update product set status=:status WHERE id in (#rc.ids#)",{status = lsparsenumber(rc.value)});
		return result;
	}

	public any function commendbat(rc){
		result = ORMExecuteQuery("update product set iscommend=:iscommend WHERE id in (#rc.ids#)",{iscommend = lsparsenumber(rc.value)});
		return result;
	}

	public any function scrollbat(rc){
		result = ORMExecuteQuery("update product set isscroll=:isscroll WHERE id in (#rc.ids#)",{isscroll = lsparsenumber(rc.value)});
		return result;
	}

	public any function commentbat(rc){
		result = ORMExecuteQuery("update product set iscomment=:iscomment WHERE id in (#rc.ids#)",{iscomment = lsparsenumber(rc.value)});
		return result;
	}

	public any function otctypebat(rc){
		result = ORMExecuteQuery("update product set otctype=:otctype WHERE id in (#rc.ids#)",{otctype = lsparsenumber(rc.value)});
		return result;
	}

	public any function medicaltypebat(rc){
		result = ORMExecuteQuery("update product set medicaltype=:medicaltype WHERE id in (#rc.ids#)",{medicaltype = lsparsenumber(rc.value)});
		return result;
	}


	public any function importFile(any rc){
			//1.先解压zip文件到upload/profiles下
			//2.将zip目录列表信息读取到uploader表中
			//3.读取excel文件
			//4.遍历excel文件
			//5.根据产品编码到uploader表中查找picdefault、pics、gmp、registerfile、qualification五个对应的材料文件
			//6.如果有相应的数据，将设置相应的值内容

            var q = entityLoadByPk("uploader", rc.excelid);
			var f = expandpath('uploads/' & q.getservername());

			var qz = entityLoadByPk("uploader", rc.prozipid);
			var fz = expandpath('uploads/' & qz.getservername());
			var unzipPath = 'uploads/profiles/' & rc.prozipid & '/';
			var unzipDes = expandpath(unzipPath);


			if(!DirectoryExists(unzipDes)){
				directory action = "create"  directory ="#unzipDes#";
			}

			//同一文件如果上传多次，则把原来的删除
			directory action = "delete" recurse="true" directory ="#unzipDes#";
			ormExecuteQuery("delete from uploader where zipid = #rc.prozipid#",true);

			zip action="unzip" destination="#unzipDes#" file="#fz#";
			directory action="list" directory="#unzipDes#" listinfo="name" recurse="true" name="zipFile";

			for(var i=1;i<=zipFile.recordcount;i++){
				var fPath = unzipPath & zipFile.name[i];
				if( fileexists( expandpath( fPath )) && len( listlast( zipFile.name[i],'.') ) <= 3){

					fileInfo = getFileInfo(expandPath(fPath));
					uploadFile.fileSize		= fileInfo.size;
					uploadFile.clientName	= fileInfo.name;
					uploadFile.fileExt		= listLast( fileInfo.name, "." );
					if(isimagefile(expandPath( fPath ))){
						imgFileInfo = imageInfo(expandPath( fPath ));
						uploadFile.fileWidth = imgFileInfo.width;
						uploadFile.fileHeight = imgFileInfo.height;
					}else{
						uploadFile.fileWidth = 0;
						uploadFile.fileHeight = 0;
					}
					uploadFile.procode= listgetat(fPath,4,'/');
					uploadFile.profiletype = listgetat(fPath,5,'/');
					uploadFile.filefullpath = fPath;
					//重命名文件
					uploadFile.serverName = hash(rand()) & "." & uploadFile.fileExt;
					renameFilePath = unzipPath & ListDeleteRight(zipFile.name[i],1,'/') & '/' & uploadFile.serverName;
					file action="rename" destination="#expandpath(renameFilePath)#" source="#expandpath(fPath)#";
					//插入db
					 var up = entityNew("uploader");
					 up.setZipid(rc.prozipid);
					 up.setClientName(uploadFile.clientName);
					 up.setServerName(uploadFile.serverName);
					 up.setFileExt(uploadFile.fileExt);
					 up.setFileSize(uploadFile.fileSize);
					 up.setFileWidth(uploadFile.fileWidth);
					 up.setFileHeight(uploadFile.fileHeight);
					 up.setUserId(session.auth.userId);
					 up.setProcode(uploadFile.procode);
					 up.setProfiletype(uploadFile.profiletype);
					 up.setFilefullpath(uploadFile.filefullpath);
					 entitySave(up);
				}
			}

			objPOI = new cfc.POIUtility();
			arrSheets = objPOI.ReadExcel( FilePath = f,HasHeaderRow = true);

			var fields = ['产品编码','产品名称','供应商编码','所属类别','产品规格','批准文号','生产厂家','包装单位','市场价格','销售价格','医保类型','是否医疗器械','产品剂型','处方类型 ','生产产地','通用名称','英文名称','拼音全码','拼音简码','产品简介','库存数量','购买数量限制','产品介绍'];

			var column = arrSheets[1].COLUMNNAMES;
			var pos = [];
			for(var i=1; i<= arraylen(column); i++){
				pos.append(arrayfindnocase(fields,column[i]));
			}

			var qry = arrSheets[1].Query;

			for(var i=1; i<= qry.RecordCount; i++){

				var procode		 		= evaluate("qry.column"  & arrayfind(pos,1) & '[' & i& ']');
				var prochsname		 	= evaluate("qry.column"  & arrayfind(pos,2) & '[' & i& ']');
				//供应商编码
				var suppliercode	 	= evaluate("qry.column"  & arrayfind(pos,3) & '[' & i& ']');
				//产品类别
				var procategory		 	= evaluate("qry.column"  & arrayfind(pos,4) & '[' & i& ']');
				var specification	 	= evaluate("qry.column"  & arrayfind(pos,5) & '[' & i& ']');
				var approvalnumber		= evaluate("qry.column"  & arrayfind(pos,6) & '[' & i& ']');
				var producingname		= evaluate("qry.column"  & arrayfind(pos,7) & '[' & i& ']');
				var packingunit		 	= evaluate("qry.column"  & arrayfind(pos,8) & '[' & i& ']');
				var marketprice		 	= evaluate("qry.column"  & arrayfind(pos,9) & '[' & i& ']');
				var saleprice		 	= evaluate("qry.column"  & arrayfind(pos,10) & '[' & i& ']');
				var medicaltype		 	= evaluate("qry.column"  & arrayfind(pos,11) & '[' & i& ']');
				var equipmenttype		= evaluate("qry.column"  & arrayfind(pos,12) & '[' & i& ']');
				var dosageform			= evaluate("qry.column"  & arrayfind(pos,13) & '[' & i& ']');
				var otctype				= evaluate("qry.column"  & arrayfind(pos,14) & '[' & i& ']');
				var producingarea		= evaluate("qry.column"  & arrayfind(pos,15) & '[' & i& ']');
				var prousuname			= evaluate("qry.column"  & arrayfind(pos,16) & '[' & i& ']');
				var proenname			= evaluate("qry.column"  & arrayfind(pos,17) & '[' & i& ']');
				var propyfullspell		= evaluate("qry.column"  & arrayfind(pos,18) & '[' & i& ']');
				var propysimplespell	= evaluate("qry.column"  & arrayfind(pos,19) & '[' & i& ']');
				var brief				= evaluate("qry.column"  & arrayfind(pos,20) & '[' & i& ']');
				var inventory			= evaluate("qry.column"  & arrayfind(pos,21) & '[' & i& ']');
				var buynumlimit			= evaluate("qry.column"  & arrayfind(pos,22) & '[' & i& ']');
				var content				= evaluate("qry.column"  & arrayfind(pos,23) & '[' & i& ']');


				query name="checkexists"{
					echo("SELECT id FROM product where procode = ");
					queryparam cfsqltype="cf_sql_varchar" value="#procode#";
					echo(" and prochsname = ");
					queryparam cfsqltype="cf_sql_varchar" value="#prochsname#";
				}

				if(checkexists.recordcount == 0){
					var q = entityNew("product");
				}else{
					var q = entityLoadByPk("product", checkexists.id);
				}

				//如果英文名称没有数据，自动转换中文名称到英文名称
				if( len(trim(proenname)) == 0){
					proenname = translateToEn(prochsname);
				}
				//中文全拼
				propyfullspell 		= getPySpell(prochsname);
				//中文拼音首字母
				propysimplespell 	= getPySpell(prochsname,1);
				//通过供应商编码找供应商ID
				var sup = entityLoad("supplier", {supcode=suppliercode},true);

				//picdefault
				query name="qpicdefault"{
					echo("SELECT id FROM uploader where procode = ");
					queryparam cfsqltype="cf_sql_varchar" value="#procode#";
					echo(" and profiletype = ");
					queryparam cfsqltype="cf_sql_varchar" value="picdefault";
				}
				//pics
				query name="qpics"{
					echo("SELECT id FROM uploader where procode = ");
					queryparam cfsqltype="cf_sql_varchar" value="#procode#";
					echo(" and profiletype = ");
					queryparam cfsqltype="cf_sql_varchar" value="pics";
				}
				//gmp
				query name="qgmp"{
					echo("SELECT id FROM uploader where procode = ");
					queryparam cfsqltype="cf_sql_varchar" value="#procode#";
					echo(" and profiletype = ");
					queryparam cfsqltype="cf_sql_varchar" value="gmp";
				}
				//registerfile
				query name="qregisterfile"{
					echo("SELECT id FROM uploader where procode = ");
					queryparam cfsqltype="cf_sql_varchar" value="#procode#";
					echo(" and profiletype = ");
					queryparam cfsqltype="cf_sql_varchar" value="registerfile";
				}
				//qualification
				query name="qqualification"{
					echo("SELECT id FROM uploader where procode = ");
					queryparam cfsqltype="cf_sql_varchar" value="#procode#";
					echo(" and profiletype = ");
					queryparam cfsqltype="cf_sql_varchar" value="qualification";
				}


				//产品分类
				for(var j=1;j<=listlen(procategory);j++){
					var pc = entityNew('productcategory');
					query name="qprocategory"{
						echo("SELECT id FROM category where text = ");
						queryparam cfsqltype="cf_sql_varchar" value="#listgetat(procategory,j)#";
					}
					pc.setcategoryid(qprocategory.id);
					q.addproductcategory(pc);
					entitySave(pc);
				}

				q.setpicdefault(qpicdefault.id);
				q.setpics(valuelist(qpics.id));
				q.setgmp(qgmp.id);
				q.setregisterfile(qregisterfile.id);
				q.setqualification(valuelist(qqualification.id));
				q.setsupplierid(sup.getId());
				q.setprocode(procode);
				q.setprochsname(prochsname);
				q.setspecification(specification);
				q.setapprovalnumber(approvalnumber);
				q.setproducingname(producingname);
				q.setpackingunit(packingunit);
				q.setmarketprice(marketprice);
				q.setsaleprice(saleprice);
				q.setmedicaltype(medicaltype);
				q.setequipmenttype(equipmenttype);
				q.setdosageform(dosageform);
				q.setotctype(otctype);
				q.setproducingarea(producingarea);
				q.setprousuname(prousuname);
				q.setproenname(proenname);
				q.setpropyfullspell(propyfullspell);
				q.setpropysimplespell(propysimplespell);
				q.setbrief(brief);
				q.setinventory(inventory);
				q.setbuynumlimit(buynumlimit);
				q.setcontent(content);
				entitySave(q);
			}

	}

}